package com.totsp.androidexamples;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

public class DataHelper {

   private static final String DATABASE_NAME = "exercise.db";
   private static final int DATABASE_VERSION = 1;
   private static final String TABLE_NAME = "exercises";

   private Context context;
   private SQLiteDatabase db;

   private SQLiteStatement insertStmt;
   private SQLiteStatement updateStmt;
   private static final String INSERT = "insert into " + TABLE_NAME + "(trials, time, last, avg, id) values (?, ?, ?, ?, ?)";
   private static final String UPDATE = "update " + TABLE_NAME + " set trials = ?, time = ?, last = ?, avg = ? where id = ?" ;
   
   public DataHelper(Context context) {
      this.context = context;
      OpenHelper openHelper = new OpenHelper(this.context);
      this.db = openHelper.getWritableDatabase();
      this.insertStmt = this.db.compileStatement(INSERT);
      this.updateStmt = this.db.compileStatement(UPDATE);
   }
   
   public SQLiteDatabase getDb() {
      return this.db;
   }

   public long insert(String id, String trials, String time) {
	  Long numTrials = Long.parseLong(trials);
	  Long timeInSeconds = Long.parseLong(time);
	  Long totNumTrials = numTrials;	  
	  Long totTimeInSeconds = timeInSeconds;
	  double avg = (double)totNumTrials/((double)totTimeInSeconds / 60.0);
	  double last = 0.0;
	  List<String[]> pastReadings = selectAll(Long.parseLong(id));
	  //Log.e("Rows", " " + pastReadings.size());
	  for(String[] str : pastReadings){
		  totNumTrials += Long.parseLong(str[0]);
		  totTimeInSeconds += Long.parseLong(str[1]);
		  avg = (double)totNumTrials/((double)totTimeInSeconds / 60.0);
		  last = (double)numTrials/((double)timeInSeconds / 60.0);
		  break; //taking only one row as this is the case now
	  }
	  if(pastReadings.size() == 0){
      this.insertStmt.bindLong(1, totNumTrials);
      this.insertStmt.bindLong(2, totTimeInSeconds);
      this.insertStmt.bindDouble(3, last);
      this.insertStmt.bindDouble(4, avg);
      this.insertStmt.bindLong(5, Long.parseLong(id));
      return this.insertStmt.executeInsert();
	  }
	  else{
		  this.updateStmt.bindLong(1, totNumTrials);
	      this.updateStmt.bindLong(2, totTimeInSeconds);
	      this.updateStmt.bindDouble(3, last);
	      this.updateStmt.bindDouble(4, avg);
	      this.updateStmt.bindLong(5, Long.parseLong(id));
	      return this.updateStmt.executeInsert();
		    
	  }
   }
   
   public void deleteAll() {
      this.db.delete(TABLE_NAME, null, null);
   }

   
   //HS -- Only one row is returned but maintaining structure for generic maintenance later
   public List<String[]> selectAll(Long id) {
      List<String[]> list = new ArrayList<String[]>();
      
      try{
      //Cursor cursor = this.db.query(TABLE_NAME, new String[] { "name" }, null, null, null, null, "name desc");
      Cursor cursor = this.db.query(TABLE_NAME, new String [] {"trials", "time", "avg", "last"}, "id="+id.toString(), null, null, null, null);
      if (cursor.moveToFirst()) {
         do {
        	String values[] = new String[4];
        	values[0] = cursor.getString(0);
        	values[1] = cursor.getString(1);
        	values[2] = cursor.getString(2);
        	values[3] = cursor.getString(3);
        	
            list.add(values); 
         } while (cursor.moveToNext());
      }
      if (cursor != null && !cursor.isClosed()) {
         cursor.close();
      }
      } catch (Exception e) {
    	  Log.e("SELECT failed","Exception in select", e);
      }
      return list;
   }
   
   private static class OpenHelper extends SQLiteOpenHelper {

      OpenHelper(Context context) {
         super(context, DATABASE_NAME, null, DATABASE_VERSION);
      }

      @Override
      public void onCreate(SQLiteDatabase db) {
         db.execSQL("CREATE TABLE " + TABLE_NAME + " (id INTEGER PRIMARY KEY, name TEXT, trials INTEGER, time INTEGER, last REAL, avg REAL, cal_burnt REAL)");
      }

      @Override
      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
         Log.w("Example", "Upgrading database, this will drop tables and recreate.");
         db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
         onCreate(db);
      }
   }
}